package fr.uniscol.dao.impl;

import java.sql.SQLException;
import java.util.List;

import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import fr.uniscol.dao.IEtablissementDao;
import fr.uniscol.dao.IGeographieDao;
import fr.uniscol.dao.UniscolDaoSupport;
import fr.uniscol.dao.mapper.EtablissementRowMapper;
import fr.uniscol.model.EtablissementModel;
import static fr.uniscol.dao.impl.SQLNames.*;

@Repository("etablissementDao")
public class EtablissementDaoImpl extends UniscolDaoSupport implements IEtablissementDao {

	@Autowired
	private IGeographieDao geoDao;
	
	@Override
	public EtablissementModel getEtablissementByUAI(String uai) {
		
		EtablissementModel etablissement = getJdbcTemplate().queryForObject("SELECT * FROM uni_etablissement WHERE uai=? ", 
												new EtablissementRowMapper(geoDao), 
												new Object[]{uai});
		
		return etablissement;
	}

	@Override
	public EtablissementModel getEtablissementById(long id) {
		EtablissementModel etablissement = getJdbcTemplate().queryForObject("SELECT * FROM uni_etablissement WHERE etablissementid=? ", 
				new EtablissementRowMapper(geoDao), 
				new Object[]{id});

		return etablissement;
	}

	@Override
	public long saveEtablissement(EtablissementModel etablissement){
		int rowsAffected = 0;
        
        if(etablissement.getId() > 0){
            rowsAffected = getJdbcTemplate().update("UPDATE  " + TABLE_ETABLISSEMENT
													+ " SET uai=?,"
													+ "nom=?,"
													+ "adresse1=?,"
													+ "adresse2=?,"
													+ "adresse3=?,"
													+ "telephone=?,"
													+ "cp=?,"
													+ "bp=?,"
													+ "email=? "
                                                    + "WHERE etablissementid=? ",
													new Object[]{
															etablissement.getUai(),
															etablissement.getNom(),
															etablissement.getAdresse().getAdresse1(),
															etablissement.getAdresse().getAdresse2(),
															etablissement.getAdresse().getAdresse3(),
															etablissement.getTelephone(),
															etablissement.getAdresse().getCodePostal(),
															etablissement.getAdresse().getBoitePostale(),
															etablissement.getEmail(),
															/*etablissement.getAdresse().getPays().getId(),
															etablissement.getAdresse().getDepartement().getId(),
															etablissement.getAcademie().getId(),
															etablissement.getAdresse().getCommune().getId(),*/
                                                            etablissement.getId()
													});
            
            return etablissement.getId();
            
        }else{
            rowsAffected = getJdbcTemplate().update("INSERT INTO " + TABLE_ETABLISSEMENT + " (uai, nom, adresse1, adresse2, adresse3, telephone, cp, bp, email) VALUES(?,?,?,?,?,?,?,?,?)",
                new Object[]{
                    etablissement.getUai(),
                    etablissement.getNom(),
                    etablissement.getAdresse().getAdresse1(),
                    etablissement.getAdresse().getAdresse2(),
                    etablissement.getAdresse().getAdresse3(),
                    etablissement.getTelephone(),
                    etablissement.getAdresse().getCodePostal(),
                    etablissement.getAdresse().getBoitePostale(),
                    etablissement.getEmail()
                });
            
            long newBaseId = getJdbcTemplate().queryForObject("SELECT LAST_INSERT_ID()", Long.class); 
            etablissement.setId(newBaseId);
            
            return newBaseId;
        }
	}

    @Override
    public List<EtablissementModel> getAllEtablissements() {
        List<EtablissementModel> etabs = getJdbcTemplate().query("SELECT * FROM " + TABLE_ETABLISSEMENT, new EtablissementRowMapper(geoDao));
        
        return etabs;
    }

    @Override
    public boolean deleteEtablissement(EtablissementModel etablissement) {
        int nbrRowsAffected = getJdbcTemplate().update("CALL SUPPRIMER_ETABLISSEMENT(?)", new Object[]{
           etablissement.getId()
        });
        
        return nbrRowsAffected == 1;
    }
}
